package com.fanyouzhao.ehome.dao;

import com.fanyouzhao.ehome.util.DBUtil;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 所有DAO层操作的通用类DAO
 *
 * @author fanyouzhao
 * @date 2021年4月2日 上午9:05:36
 */
public class BaseDao<T> {

    private DBUtil dbutil = new DBUtil();// 引入DBUtil

    // 增系列
    public Integer insert(T t) throws Exception {
        Class<?> clazz = t.getClass();
        String simpleName = clazz.getSimpleName();
        Field[] fields = clazz.getDeclaredFields();

        StringBuilder builder = new StringBuilder("INSERT INTO tb_" + simpleName + " VALUES(NULL");
        for (int i = 1; i < fields.length; i++) {
            builder.append(",?");
        }
        builder.append(")");

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(builder.toString());

        for (int i = 1; i < fields.length; i++) {
            fields[i].setAccessible(true);
            preparedStatement.setObject(i, fields[i].get(t));
        }

        int lines = preparedStatement.executeUpdate();
        dbutil.closeAll();
        return lines;

    }

    // 删系列
    public Integer deleteById(Integer id, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String delete = "DELETE FROM tb_" + simpleName + " WHERE " + simpleName + "_id=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(delete);
        preparedStatement.setObject(1, id);

        int lines = preparedStatement.executeUpdate();
        dbutil.closeAll();
        return lines;// 返回影响的行数
    }

    public Integer deleteByUsername(String username, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String delete = "DELETE FROM tb_" + simpleName + " WHERE " + simpleName + "_username=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(delete);
        preparedStatement.setObject(1, username);

        int lines = preparedStatement.executeUpdate();
        dbutil.closeAll();
        return lines;// 返回影响的行数
    }

    // 改系列
    public Integer update(Integer id, T t) throws Exception {

        Class<?> clazz = t.getClass();
        String simpleName = clazz.getSimpleName();
        Field[] fields = clazz.getDeclaredFields();

        StringBuilder builder = new StringBuilder("UPDATE tb_" + simpleName + " SET ");
        for (int i = 1; i < fields.length; i++) {
            fields[i].setAccessible(true);
            builder.append(simpleName + "_" + fields[i].getName() + "=?,");
        }
        builder.deleteCharAt(builder.length() - 1);// 删除最后多余的逗号,
        builder.append(" WHERE " + simpleName + "_id=? ");

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(builder.toString());

        for (int i = 1; i < fields.length; i++) {
            fields[i].setAccessible(true);
            preparedStatement.setObject(i, fields[i].get(t));
            preparedStatement.setObject(fields.length, id);
        }

        int lines = preparedStatement.executeUpdate();
        dbutil.closeAll();
        return lines;
    }

    // 查系列
    public T selectById(Integer id, Class<T> clazz) throws Exception {
        // 数据库的名称各不相同，必须通过Class体现
        // 1.通过clazz获取类的简单名称：User、Fund
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT *  FROM tb_" + simpleName + " WHERE " + simpleName + "_id=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, id);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        // 创建一个目标对象（User、Fund）
        T t = null;// 利用泛型类型声明，注意不能实例化
        if (resultSet.next()) {
            // 给目标对象实例化，采用反射方式
            t = clazz.getConstructor().newInstance();

            // 给对象赋值
            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
        }
        dbutil.closeAll();
        return t;
    }

    // 使用反射获取方法以查询
	/*	public T selectById(Integer id, Class<T> clazz) throws Exception {
			String simpleName = clazz.getSimpleName();
			String sql = "SELECT *  FROM tb_" + simpleName + " WHERE " + simpleName + "_id=?";
	
			PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
			preparedStatement.setObject(1, id);
			ResultSet resultSet = dbutil.execQuery(preparedStatement);
	
			T t = null;
			if (resultSet.next()) {
				Field[] fields = clazz.getDeclaredFields();
				t = clazz.getConstructor().newInstance();
				for (Field field : fields) {
					String fieldName = field.getName();
					if (fieldName.startsWith("is")) {
	
					} else {
						fieldName = fieldName.replace(fieldName.charAt(0), Character.toUpperCase(fieldName.charAt(0)));
						fieldName = "set" + fieldName;
						Method method = clazz.getDeclaredMethod(fieldName, field.getType());
	
						String columnName = simpleName + "_" + field.getName();
						method.invoke(t, resultSet.getObject(columnName));
					}
				}
			}
			dbutil.closeAll();
			return t;
		}*/

    public T selectByCode(String code, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT *  FROM tb_" + simpleName + " WHERE " + simpleName + "_code=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, code);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        T t = null;
        if (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
        }
        dbutil.closeAll();
        return t;
    }

    public T selectByName(String name, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT *  FROM tb_" + simpleName + " WHERE " + simpleName + "_name=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, name);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        T t = null;
        if (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }

        }
        dbutil.closeAll();
        return t;
    }

    public T selectByPhone(String phone, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT *  FROM tb_" + simpleName + " WHERE " + simpleName + "_phone=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, phone);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        T t = null;
        if (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
        }
        dbutil.closeAll();
        return t;
    }

    public T selectByUsername(String name, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT *  FROM tb_" + simpleName + " WHERE " + simpleName + "_username=?";

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setObject(1, name);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        T t = null;
        if (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }

        }
        dbutil.closeAll();
        return t;
    }

    public List<T> selectByPage(int page, int size, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT * FROM tb_" + simpleName + " ORDER BY " + simpleName + "_id DESC LIMIT ?,?";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setInt(1, (page - 1) * size);
        preparedStatement.setInt(2, size);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<T> list = new ArrayList<T>();
        T t = null;
        while (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
            list.add(t);
        }
        dbutil.closeAll();
        return list;
    }

    public List<T> selectLikeName(String name, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT * FROM tb_" + simpleName + " WHERE " + simpleName + "_name" + " LIKE ?";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setString(1, "%" + name + "%");
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<T> list = new ArrayList<T>();
        T t = null;
        while (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
            list.add(t);
        }
        dbutil.closeAll();
        return list;
    }

    public List<T> selectLikeNameByPage(String name, int page, int size, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT * FROM tb_" + simpleName + " WHERE " + simpleName + "_name" + " LIKE ? LIMIT ?,?";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setString(1, "%" + name + "%");
        preparedStatement.setInt(2, (page - 1) * size);
        preparedStatement.setInt(3, size);

        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<T> list = new ArrayList<T>();
        T t = null;
        while (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
            list.add(t);
        }
        dbutil.closeAll();
        return list;
    }

    public List<T> selectAll(Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT * FROM tb_" + simpleName;

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<T> list = new ArrayList<T>();
        T t = null;
        while (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
            list.add(t);

        }
        dbutil.closeAll();
        return list;
    }

    public List<T> selectLikeNameorPhoneByPage(String searchString, int page, int size, Class<T> clazz) throws Exception {
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT * FROM tb_" + simpleName + " WHERE " + simpleName + "_name LIKE ? OR " + simpleName + "_phone LIKE ? ORDER BY " + simpleName + "_id DESC LIMIT ?,?";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setString(1, "%" + searchString + "%");
        preparedStatement.setString(2, "%" + searchString + "%");
        preparedStatement.setInt(3, (page - 1) * size);
        preparedStatement.setInt(4, size);

        ResultSet resultSet = dbutil.execQuery(preparedStatement);

        List<T> list = new ArrayList<T>();
        T t = null;
        while (resultSet.next()) {
            t = clazz.getConstructor().newInstance();

            Field[] fields = clazz.getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                field.set(t, resultSet.getObject(simpleName + "_" + field.getName()));
            }
            list.add(t);
        }
        dbutil.closeAll();
        return list;
    }

    public Integer getCount(Class<T> clazz) throws Exception {
        int count = 0;
        String simpleName = clazz.getSimpleName();
        String sql = "SELECT COUNT(*) FROM tb_" + simpleName;

        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        ResultSet resultSet = dbutil.execQuery(preparedStatement);
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        dbutil.closeAll();
        return count;
    }

    public Integer getCountSearch(String searchString, Class<T> clazz) throws Exception {
        int count = 0;
        String simpleName = clazz.getSimpleName();

        String sql = "SELECT COUNT(*) FROM tb_" + simpleName + " WHERE " + simpleName + "_name LIKE ? OR " + simpleName + "_phone LIKE ?";
        PreparedStatement preparedStatement = dbutil.getPreparedStatement(sql);
        preparedStatement.setString(1, "%" + searchString + "%");
        preparedStatement.setString(2, "%" + searchString + "%");

        ResultSet resultSet = dbutil.execQuery(preparedStatement);
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        dbutil.closeAll();
        return count;
    }

}
